﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using UPos.Core.Entity;

namespace UPos.Core.Table
{
    public class Sales
    {
        #region SQLs

        #region SALES
static string SALES = 
@"Select
a.fid FID
, a.fbizdate BizDate
, a.cfmachineno MachineNo
, a.fnumber BillNumber
, u.fnumber CreaterNumber
, u.fname_l2 CreaterName
, sum(b.cfamount) Amount
, a.cfpaymentmoney Paymentmoney
FROM CT_BIL_RetailPos A 
JOIN Ct_Bil_Retailposentry b on a.fid = b.fparentid
join T_PM_User u on u.fid = a.fcreatorid
where a.cfstorageid = :shopfid
and to_char(a.fbizdate, 'yyyy-mm-dd') = :saledate
[upedFidWhere]
group by 
a.fid
, a.fbizdate 
, a.cfmachineno 
, a.fnumber 
, u.fnumber 
, u.fname_l2
, a.cfpaymentmoney
order by a.fid";
        #endregion

        #region SALEITEMS
static string SALEITEMS = 
@"Select 
b.fparentid FParentID
, e.fnumber ""Number""
, b.cfamount Qty
, b.cfagio_sum_price TotalPrice
FROM CT_BIL_RetailPosEntry B 
JOIN T_BD_Material E ON B.CFMaterialID = E.FID
where fparentid in (:parentids)
order by b.fparentid";
        #endregion

        #region RECEIVEITEMS
        static string RECEIVEITEMS = @"
select 
a.fparentid FParentID
, a.Cfhave_pay_money PayMoney
, r.fname_l2 TypeName
, r.fnumber TypeID
from CT_BIL_RetailPRPRE a
join CT_SBA_ShopReceivetype r on a.cfpaymenttype = r.fid
where fparentid in (:parentids)
order by a.fparentid";
        #endregion

        #endregion

        static Sales()
        {
#warning 暂用新世界
            ShopFID = "JrQcR6EvTDSH63cExRFM57vp+K4=";
        }

        public static string ShopFID { get; set; }

        public Sales(DateTime date)
        {
            SaleDate = date;
        }

        /// <summary>
        /// 销售日期
        /// </summary>
        public DateTime SaleDate { get; set; }

        DbOracle _oracle;
        DbOracle Oracle
        {
            get
            {
                if (_oracle == null)
                    _oracle = new DbOracle();
                return _oracle;
            }
        }

        DbSQLServer _sqlServer;
        DbSQLServer SQLServer
        {
            get
            {
                if (_sqlServer == null)
                    _sqlServer = new DbSQLServer();
                return _sqlServer;
            }
        }

        public int GetAllCount()
        {
            var count = Oracle.ExecuteScalar(
                "select count(1) from CT_BIL_RetailPos Where to_char(fbizdate, 'yyyy-mm-dd')=:saledate and Cfstorageid=:shopid",
                "saledate".OP(SaleDate.ToString("yyyy-MM-dd")),
                "shopid".OP(ShopFID));

            return Convert.ToInt32(count);
        }

        public int GetUpedCount()
        {
            var count = SQLServer.ExecuteScalar(
                "select count(1) from Sales where BizDate=@date and StorageID=@shop",
                "date".SP(SaleDate),
                "shop".SP(ShopFID));

            return Convert.ToInt32(count);
        }

        public bool NeedUp()
        {
            return GetAllCount() > GetUpedCount();
        }

        public List<string> GetUpedFid()
        {
            using (SqlDataReader reader = SQLServer.ExecuteReader("select FID from sales where BizDate = @date", "date".SP(SaleDate)))
            {
                var list = new List<string>();
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                }
                return list;
            }
        }

        public string GenerateUpedFidWhere(List<string> upedFids)
        {
            var preWhere = string.Join("' and fid <> '", upedFids);
            if (string.IsNullOrWhiteSpace(preWhere))
                return string.Empty;
            else
                return "fid <> '" + preWhere + "'";
        }

        public List<Sale> GetNeedUp()
        {
            var upedFids = GetUpedFid();
            var upedFidWhere = GenerateUpedFidWhere(upedFids);
            string sql = SALES.Replace("[upedFidWhere]", upedFidWhere);
            StringBuilder builder = new StringBuilder("'");
            List<Sale> list = new List<Sale>();
            using (OracleDataReader reader = Oracle.ExecuteReader(sql, "shopfid".OP(ShopFID), "saledate".OP(SaleDate.ToString("yyyy-MM-dd"))))
            {
                while (reader.Read())
                {
                    list.Add(new Sale(reader));
                    builder.Append(list[list.Count - 1].FID);
                    builder.Append("','");
                }
                builder.Remove(builder.Length - 2, 2);
            }

            using (OracleDataReader entriesReader = Oracle.ExecuteReader(SALEITEMS.Replace(":parentids", builder.ToString())))
            {
                int index = 0;
                List<SaleEntry> entries = null;
                string parentid = null;
                while (entriesReader.Read())
                {
                    parentid = entriesReader.GetString(entriesReader.GetOrdinal("FParentID"));
                    if (entries == null || entries[0].FParentID != parentid)
                    {
                        entries = new List<SaleEntry>();
                        list[index].SaleItems = entries;
                        index++;
                    }
                    entries.Add(new SaleEntry(entriesReader));
                }
            }

            using (OracleDataReader entriesReader = Oracle.ExecuteReader(RECEIVEITEMS.Replace(":parentids", builder.ToString())))
            {
                int index = 0;
                List<ReceiveEntries> entries = null;
                string parentid = null;
                while (entriesReader.Read())
                {
                    parentid = entriesReader.GetString(entriesReader.GetOrdinal("FParentID"));
                    if (entries == null || entries[0].FParentID != parentid)
                    {
                        entries = new List<ReceiveEntries>();
                        list[index].ReceiveItems = entries;
                        index++;
                    }
                    entries.Add(new ReceiveEntries(entriesReader));
                }
            }

            return list;
        }

        public void SaveUnUp(List<Sale> salesList, DateTime upDate)
        {
            DataTable tab = SQLServer.GetTableSchema("Sales");
            using (tab)
            {
                foreach (var item in salesList)
                {
                    tab.Rows.Add(
                        item.FID
                        , item.BizDate
                        , item.MachineNo
                        , item.BillNumber
                        , item.CreaterNumber
                        , item.CreaterName
                        , item.Amount
                        , item.Paymentmoney
                        , ShopFID
                        , upDate);
                }
                using (SqlBulkCopy sbc = 
                    new SqlBulkCopy(DbSQLServer.DefaultConnectionString))
                {
                    sbc.DestinationTableName = "Sales";
                    sbc.WriteToServer(tab);
                }
            }

            DataTable SaleEntries = SQLServer.GetTableSchema("SaleEntries");
            using (SaleEntries)
            {
                foreach (var item in salesList)
                {
                    foreach (var entry in item.SaleItems)
                    {
                        SaleEntries.Rows.Add(
                            entry.FParentID,
                            entry.Number,
                            entry.Qty,
                            entry.TotalPrice);
                    }
                }
                using (SqlBulkCopy sbc =
                    new SqlBulkCopy(DbSQLServer.DefaultConnectionString))
                {
                    sbc.DestinationTableName = "SaleEntries";
                    sbc.WriteToServer(SaleEntries);
                }
            }

            DataTable ReceiveEntries = SQLServer.GetTableSchema("ReceiveEntries");
            using (ReceiveEntries)
            {
                foreach (var item in salesList)
                {
                    foreach (var entry in item.ReceiveItems)
                    {
                        ReceiveEntries.Rows.Add(
                            entry.FParentID
                            , entry.PayMoney
                            , entry.PaymentType.ID
                            , entry.PaymentType.Name);
                    }
                }
                using (SqlBulkCopy sbc =
                    new SqlBulkCopy(DbSQLServer.DefaultConnectionString))
                {
                    sbc.DestinationTableName = "ReceiveEntries";
                    sbc.WriteToServer(ReceiveEntries);
                }
            }
        }
    }
}